Kevin-Blog
  • 表结构

# 书
class Book(models.Model):
    title = models.CharField(max_length=32)
    publish_date = models.DateField(auto_now_add=True)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    memo = models.TextField(null=True)
# 创建外键，关联publish
    publisher = models.ForeignKey(to="Publisher")
 # 创建多对多关联author
    author = models.ManyToManyField(to="Author")

    def __str__(self):
        return self.title


# 出版社
class Publisher(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)

    def __str__(self):
        return self.name


# 作者
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    phone = models.CharField(max_length=11)
# 创建一对一关联authorDetail
    detail = models.OneToOneField(to="AuthorDetail")

    def __str__(self):
        return self.name


# 作者详情
class AuthorDetail(models.Model):
    addr = models.CharField(max_length=64)
    email = models.EmailField()


  • 查找所有书名里包含"西"的书

book = Book.objects.filter(title__contains='西')

  • 查找出版日期是2017年的书

book = Book.objects.filter(publish_date__year=2017)

  • 查找出版日期是2017年的书名

book = Book.objects.filter(publish_date__year=2017).values('title')

  • 查找价格大于10元的书

book = Book.objects.filter(price__gt=10)

  • 查找价格大于10元的书名和价格

book = Book.objects.filter(price__gt=10).values('title', 'price')

  • 查找memo字段是空的书

book = Book.objects.filter(memo__isnull=True)

  • 查找在东莞的出版社

publisher = Publisher.objects.filter(city='东莞')

  • 查找名字以'东'开头的出版社

publisher = Publisher.objects.filter(city__startswith='东')

  • 查找作者名字里面带“小”字的作者

author = Author.objects.filter(name__contains='小')

  • 查找年龄大于30岁的作者

author = Author.objects.filter(age__gt=30)

  • 查找手机号是130开头的作者

author = Author.objects.filter(phone__startswith='130')

  • 查找手机号是130开头的作者的姓名和年龄

author = Author.objects.filter(phone__startswith='130').values('name', 'phone')

  • 查找书名是“番茄物语”的书的出版社

publisher_obj = Book.objects.get(title='西游记').publisher

  • 查找书名是“番茄物语”的书的出版社所在的城市

publisher_city = Book.objects.get(title='西游记').publisher.city

  • 查找书名是“番茄物语”的书的出版社的名称

publisher_name = Book.objects.get(title='西游记').publisher.name

  • 查找书名是“西游记”的书的所有作者

authors_list = Book.objects.get(title='西游记').author.all()

  • 查找书名是“西游记”的书的作者的年龄和手机号码

    • 写法一

author_list = Book.objects.get(title='西游记').author.all()

for author in author_list:
    print(author.name, author.age, author.phone)

    • 写法二

author_list = Book.objects.filter(title='西游记').values('author__name', 'author__age', 'author__phone')

  • 查找书名是“西游记”的书的作者的年龄和手机号码和作者详情id -> 跨多张表进行查询

    • 写法一

author_list = Book.objects.get(title='西游记').author.all()

for author in author_list:
    print(author.name, author.detail_id, author.age, author.phone)

    • 写法二

author_list = Book.objects.filter(title='西游记').values('author__name', 'author__detail_id', 'author__detail__addr', 'author__detail__email')



← ORM-聚合查询 →